package com.nabton.ndscollector.database;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.nabton.ndscollector.database.DatabaseHelper;
import com.nabton.ndscollector.values.NabControl;
import com.nabton.ndscollector.values.NabStatus;

public class DatabaseManager {

	/*--Static definitions for database structure--e*/	
	private static final String NABTON_MEASUREMENT_TABLE_STREAM_ID = "stream_"; 
	private static final String NABTON_MEASUREMENT_COLUMN_DATA = "data";
	private static final String NABTON_MEASUREMENT_COLUMN_TIMESTAMP = "time";
	
	private static final String NABTON_CONTROL_TABLE_CONTROL_ID = "table_"; 
	private static final String NABTON_CONTROL_COLUMN_TAG = "tag";
	private static final String NABTON_CONTROL_COLUMN_VALUE = "value";
	
	private Connection c;
	private List<String> streamTableDef = new ArrayList<>();
	private List<String> streamTableColumns = new ArrayList<>();
	
	private List<String> controlTableColumns = new ArrayList<>();
	
	/**
	 * Public methods for DatabaseManager
	 */
	public DatabaseManager()
	{
        //Set connection to null
        c = null;
        streamTableDef.add(NABTON_MEASUREMENT_COLUMN_DATA      + " REAL NOT NULL");
        streamTableDef.add(NABTON_MEASUREMENT_COLUMN_TIMESTAMP + " TEXT NOT NULL"); 
        
        streamTableColumns.add(NABTON_MEASUREMENT_COLUMN_DATA);
        streamTableColumns.add(NABTON_MEASUREMENT_COLUMN_TIMESTAMP);
        
        controlTableColumns.add(NABTON_CONTROL_COLUMN_TAG);
        controlTableColumns.add(NABTON_CONTROL_COLUMN_VALUE);
	}
	
	public NabStatus openDatabase(String databaseName)
	{
	    try 
	    {
	    	Class.forName("org.sqlite.JDBC");
	        c = DriverManager.getConnection("jdbc:sqlite:" + databaseName);
	        return NabStatus.OK;
	    } 
	    catch ( Exception e ) 
	    {
	        System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	        return NabStatus.FAILED;
	    }	    
	}
	
	
	public NabStatus closeDatabase()
	{
	    try
	    {
            c.close();
            return NabStatus.OK;
	    } 
	    catch ( Exception e ) 
	    {
	        System.err.println( e.getClass().getName() + ": " + e.getMessage() );
	        return NabStatus.FAILED;
	    }
	}
	
	public NabStatus addMeasurement(
        int streamId,
        double data)
    {
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd'T'HHmmss");
        String timeStamp = sdf.format(cal.getTime());
	    
        try
        { 
            Statement stmt = c.createStatement();
            String tableName = NABTON_MEASUREMENT_TABLE_STREAM_ID+streamId;
            //stmt.executeQuery(DatabaseHelper.createExistsTableQuery(tableName));
            stmt.execute(DatabaseHelper.createInsertQuery(tableName, data, timeStamp));
            
            System.out.println("**" + streamId + " | " + data + " | " + timeStamp);
            
            return NabStatus.OK;
        } 
        catch ( Exception e ) 
        {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return NabStatus.FAILED;
        }
    }
	
	/**
	 * Set up new table for stream with id sid
	 */
	public NabStatus setupNewStream(int sid)
	{
	    String tableName = NABTON_MEASUREMENT_TABLE_STREAM_ID+sid;
	    int c1;
	    
	    //Check for connection
	    if (c == null)
	    {
	        System.out.println("#No connection to database exists");
	        return NabStatus.FAILED;
	    }
	    //Check if table already exists
	    try {
    	    Statement stmt = c.createStatement();
    	    ResultSet rs = stmt.executeQuery(DatabaseHelper.createExistsTableQuery(tableName));
            rs.next();
            c1 = rs.getInt(1);
            rs.close();
            stmt.close();
	    }
	    catch (SQLException e) {
            e.printStackTrace();
            return NabStatus.FAILED;
        }
        
	    //If not create it
        if(c1 == 0){
            try {
                Statement stmt = c.createStatement();
                stmt.executeUpdate(DatabaseHelper.createTableQuery(
                    tableName,
                    streamTableColumns));
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return NabStatus.FAILED;
            }
        }
        
        return NabStatus.OK;
	}
	
	   /**
     * Check and fetch control message for sid
     */
    public List<NabControl> fetchControlMessage(int sid)
    {
        String tableName = NABTON_CONTROL_TABLE_CONTROL_ID+sid;
        int c1 = 0;
        
        List<NabControl> controlMessages = new ArrayList<NabControl>();
        
        //Check for connection
        if (c == null)
        {
            System.out.println("#No connection to database exists");
            return controlMessages;
        }
        //Check if table already exists
        try {
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery(DatabaseHelper.createExistsTableQuery(tableName));
            rs.next();
            c1 = rs.getInt(1);
            rs.close();
            stmt.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        
        //If not create it
        if(c1 == 0){
            System.out.println("#Control database does not contain messages for sid:" + sid);
        }
        else
        {
            try {
                Statement stmt = c.createStatement();
                ResultSet rs = stmt.executeQuery(DatabaseHelper.createSelectAllNewControlMessagesQuery(tableName));
                while(rs.next())
                {
                    int value  = rs.getInt(2);
                    String tag = rs.getString(1);
                    controlMessages.add(new NabControl(value, tag));
                }
                rs.close();
                stmt.close();
                
                //TODO implement boolean true false if success?
                stmt = c.createStatement();
                String query = "UPDATE " + tableName + " SET status='EXECUTED' WHERE status='NEW'";
                stmt.execute(query);
                stmt.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
            
        }
        
        return controlMessages;
    }
}

